/**
 * MySQL 数据库 t_product_videos 表初始化脚本
 * 用于创建商品视频信息表
 */

const mysql = require('mysql2/promise');
require('dotenv').config();

const DB_CONFIG = {
    host: process.env.DB_HOST || 'localhost',
    port: process.env.DB_PORT || 3306,
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'tkshop'
};

/**
 * 初始化商品视频表
 */
async function initializeProductVideosTable() {
    let connection;

    try {
        console.log('连接到 MySQL 数据库...');
        connection = await mysql.createConnection(DB_CONFIG);
        console.log('数据库连接成功');

        // 创建 t_product_videos 表
        await createProductVideosTable(connection);

        console.log('t_product_videos 表初始化完成！');

    } catch (error) {
        console.error('t_product_videos 表初始化失败:', error);

        // 提供更详细的错误信息
        if (error.code === 'ER_ACCESS_DENIED_ERROR') {
            console.log('💡 请检查数据库用户名和密码是否正确');
        } else if (error.code === 'ECONNREFUSED') {
            console.log('💡 请确保 MySQL 服务正在运行');
            console.log('   macOS: brew services start mysql');
            console.log('   Linux: sudo systemctl start mysql');
        } else if (error.code === 'ER_BAD_DB_ERROR') {
            console.log('💡 数据库不存在，请先运行主初始化脚本创建数据库');
        }

        process.exit(1);
    } finally {
        if (connection) {
            await connection.end();
            console.log('MySQL 连接已关闭');
        }
    }
}

/**
 * 创建商品视频表
 */
async function createProductVideosTable(connection) {
    try {
        console.log('创建 t_product_videos 表...');

        // 检查表是否已存在
        const [tables] = await connection.query(`
      SELECT TABLE_NAME 
      FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 't_product_videos'
    `, [DB_CONFIG.database]);

        if (tables.length > 0) {
            console.log('t_product_videos 表已存在，删除后重新创建...');
            await connection.query('DROP TABLE t_product_videos');
        }

        const createTableSQL = `
      CREATE TABLE t_product_videos (
        id VARCHAR(50) NOT NULL PRIMARY KEY COMMENT '视频ID（接口获取的视频ID）',
        origin_cover TEXT NULL COMMENT '视频封面图URL',
        product_id VARCHAR(50) NOT NULL COMMENT '关联商品ID',
        \`desc\` TEXT NULL COMMENT '视频描述',
        video_duration INT NULL COMMENT '视频时长（秒）',
        video_link VARCHAR(500) NULL COMMENT '视频链接',
        total_estimated_sales_amount DECIMAL(15,2) NULL COMMENT '预估销售额（GMV）',
        total_estimated_sales_volume INT NULL COMMENT '预估销量（Order）',
        play_count BIGINT NULL COMMENT '播放量',
        digg_count BIGINT NULL COMMENT '点赞数',
        comment_count BIGINT NULL COMMENT '评论数',
        share_count BIGINT NULL COMMENT '分享数',
        author_last_time VARCHAR(50) NULL COMMENT '发布日期',
        author_last_time_stamp TIMESTAMP NULL COMMENT '发布日期时间戳',
        author_id VARCHAR(100) NULL COMMENT 'KOL ID',
        author_unique_id VARCHAR(100) NULL COMMENT 'KOL唯一ID',
        author_nickname VARCHAR(200) NULL COMMENT 'KOL昵称',
        sys_country_country VARCHAR(100) NULL COMMENT '国家英文名',
        sys_country_country_cn VARCHAR(100) NULL COMMENT '国家中文名',
        sys_country_country_code VARCHAR(10) NULL COMMENT '国家代码',
        author_follower_count BIGINT NULL COMMENT 'KOL粉丝数',
        author_homepage VARCHAR(500) NULL COMMENT 'KOL账号主页',
        createTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        
        -- 外键约束
        CONSTRAINT fk_product_videos_product_id 
          FOREIGN KEY (product_id) REFERENCES t_product(id) 
          ON DELETE CASCADE ON UPDATE CASCADE
          
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='TikTok商品视频信息表'
    `;

        await connection.query(createTableSQL);
        console.log('t_product_videos 表创建成功');

        // 创建索引
        await createVideoIndexes(connection);

    } catch (error) {
        console.error('创建 t_product_videos 表失败:', error);
        throw error;
    }
}

/**
 * 创建索引
 */
async function createVideoIndexes(connection) {
    try {
        console.log('创建索引...');

        const indexes = [
            'CREATE INDEX idx_product_videos_product_id ON t_product_videos (product_id)',
            'CREATE INDEX idx_product_videos_author_id ON t_product_videos (author_id)',
            'CREATE INDEX idx_product_videos_author_unique_id ON t_product_videos (author_unique_id)',
            'CREATE INDEX idx_product_videos_author_nickname ON t_product_videos (author_nickname)',
            'CREATE INDEX idx_product_videos_play_count ON t_product_videos (play_count)',
            'CREATE INDEX idx_product_videos_digg_count ON t_product_videos (digg_count)',
            'CREATE INDEX idx_product_videos_comment_count ON t_product_videos (comment_count)',
            'CREATE INDEX idx_product_videos_share_count ON t_product_videos (share_count)',
            'CREATE INDEX idx_product_videos_author_follower_count ON t_product_videos (author_follower_count)',
            'CREATE INDEX idx_product_videos_total_estimated_sales_amount ON t_product_videos (total_estimated_sales_amount)',
            'CREATE INDEX idx_product_videos_total_estimated_sales_volume ON t_product_videos (total_estimated_sales_volume)',
            'CREATE INDEX idx_product_videos_author_last_time_stamp ON t_product_videos (author_last_time_stamp)',
            'CREATE INDEX idx_product_videos_sys_country_country ON t_product_videos (sys_country_country)',
            'CREATE INDEX idx_product_videos_sys_country_code ON t_product_videos (sys_country_country_code)',
            'CREATE INDEX idx_product_videos_create_time ON t_product_videos (createTime)',
            'CREATE INDEX idx_product_videos_update_time ON t_product_videos (updateTime)'
        ];

        for (const indexSQL of indexes) {
            try {
                await connection.query(indexSQL);
            } catch (error) {
                if (error.code === 'ER_DUP_KEYNAME') {
                    // 索引已存在，忽略错误
                    continue;
                }
                throw error;
            }
        }

        console.log('索引创建完成');

    } catch (error) {
        console.error('创建索引失败:', error);
    }
}

/**
 * 插入示例数据
 */
async function insertSampleVideoData(connection) {
    try {
        console.log('检查是否需要插入示例数据...');

        // 检查表是否为空
        const [rows] = await connection.query('SELECT COUNT(*) as count FROM t_product_videos');
        const count = rows[0].count;

        if (count > 0) {
            console.log(`表中已有 ${count} 条数据，跳过示例数据插入`);
            return;
        }

        console.log('插入示例数据...');

        const sampleData = [
            [
                '7400123456789012345',
                'https://p16-sign.tiktokcdn.com/obj/example-cover1.jpg',
                '1731808168774042656',
                '这是一个很棒的商品推荐视频！#好物推荐 #TikTok购物',
                30,
                'https://www.tiktok.com/@demo_user1/video/7400123456789012345',
                15000.50,
                500,
                1234567,
                12345,
                678,
                234,
                '2024-01-15 10:30:00',
                '2024-01-15 10:30:00',
                'author123456',
                'demo_user1',
                '演示用户1',
                'United States',
                '美国',
                'US',
                850000,
                'https://www.tiktok.com/@demo_user1'
            ],
            [
                '7400123456789012346',
                'https://p16-sign.tiktokcdn.com/obj/example-cover2.jpg',
                '1731808168774042656',
                '超值好货分享，必须推荐给大家！#购物分享 #种草',
                45,
                'https://www.tiktok.com/@demo_user2/video/7400123456789012346',
                8500.80,
                280,
                567890,
                8910,
                345,
                156,
                '2024-01-16 14:20:00',
                '2024-01-16 14:20:00',
                'author789012',
                'demo_user2',
                '演示用户2',
                'United Kingdom',
                '英国',
                'GB',
                420000,
                'https://www.tiktok.com/@demo_user2'
            ]
        ];

        const insertSQL = `
      INSERT INTO t_product_videos (
        id, origin_cover, product_id, \`desc\`, video_duration, video_link,
        total_estimated_sales_amount, total_estimated_sales_volume,
        play_count, digg_count, comment_count, share_count,
        author_last_time, author_last_time_stamp,
        author_id, author_unique_id, author_nickname,
        sys_country_country, sys_country_country_cn, sys_country_country_code,
        author_follower_count, author_homepage
      ) VALUES ?
    `;

        await connection.query(insertSQL, [sampleData]);
        console.log(`成功插入 ${sampleData.length} 条示例数据`);

    } catch (error) {
        console.error('插入示例数据失败:', error);
        console.log('注意：如果商品表中没有对应的 product_id，请先运行主初始化脚本');
    }
}

// 运行初始化
if (require.main === module) {
    initializeProductVideosTable();
}

module.exports = {
    initializeProductVideosTable,
    createProductVideosTable,
    insertSampleVideoData
};